library(tidyverse)
library(fpp3)
library(lubridate)
library(plotly)
library(kableExtra)
E-Commerce platforms has been growing in popularity as by leveraging on technology, goods and services can reach out to more consumers both domestically and internationally and thus facilitates business development. The following analysis is on a one year data collected from an UK based E-commerce platform to identify trends, analyze key metrics and extract useful insights that supports the formation of strategies for the platforms future growth.
#Date Formatting
Completed_Transactions <- Completed_Transactions %>%
mutate(Date = as.Date(Date, format = '%m/%d/%Y')) %>% arrange(Date)
# Dataset Preparation for Year Month
Sales_Trend <- Completed_Transactions %>%
group_by(yearmonth(Date)) %>%
summarise(Total_Sales = n()) %>%
rename("Year_Month" = "yearmonth(Date)")
# Obtaining the last
Last_Date <- Completed_Transactions %>% filter(Date >= "2019-12-01" & Date <= "2019-12-31") %>% tail(1)
# Sales Trend Visualisation
ggplotly(Sales_Trend %>% ggplot(aes(x = Year_Month,
y = Total_Sales)) +
geom_line() +
ggtitle("Sales Trend December 2018 - December 2019") +
xlab("Date"))
The line chart below shows that there is an overall positive trend in the sales over the one year time frame, however, its worth to take not that the massive drop in sales during December 2019 was because the last data available was on 2019-12-09. Thus, it does not reflect the actual performance during December 2019.
# Identifying the top 3 most purchased products
Top3_Products <- Completed_Transactions %>% group_by(ProductName) %>%
tally() %>%
rename("No_Times_Purchased" = 'n') %>%
arrange(-No_Times_Purchased) %>%
head(3)
# Bar Chart showing the top 3 products sold from December 2018 to December 2019
ggplotly(ggplot(Top3_Products, aes(x = ProductName, y = No_Times_Purchased, fill = ProductName)) +
geom_bar(stat="identity") +
ggtitle("Top 3 Products Purchased December 2018 to December 2019") +
theme(axis.text.x = element_blank()))
Based on the analysis, the top 3 products being purchased in the year are Cream Hanging Heart T-Light Holder, Jumbo Bag Red Retrospot, Regency Cakestand 3 Tier. The sales volume of the top 3 products are of similar quantity.
# Identifying the top 3 products sold
Top3_Products_Info <- Completed_Transactions %>%
filter(ProductName == Top3_Products$ProductName) %>%
group_by(ProductName, yearmonth(Date)) %>%
rename("Year_Month" = "yearmonth(Date)") %>%
summarise(Total_Sales = n()) %>%
arrange(ProductName)
# Visualizing the top 3 products sales
ggplotly(ggplot(Top3_Products_Info, aes(x = Year_Month,
y = Total_Sales,
group = ProductName,
color = ProductName)) +
geom_line() +
ggtitle("Top 3 Products Sales Trend December 2018 - December 2019"))
It seems that there are some similar short term patterns between sales trend of the top 3 products, However, these patterns does not remain consistent over the year. Interestingly, it does look similar to the general sales trend of the year in the e-commerce platform. However, this may be due to random chances.
3 different Key Performance Indicators will be used to measure the value, quantity and frequency of transactions from the top performing countries
# Average Order Value (Total Revenues Divide By Distinct Transactions)
# Added a total revenue column to the dataset
Completed_Transactions$Total_Revenue <- (Completed_Transactions$Quantity * Completed_Transactions$Price)
# Identifying the average order value of the top 5 countries
Top_Average_Order_Value <- Completed_Transactions %>%
group_by(Country) %>%
summarise(Average_Order_Value = sum(Total_Revenue)/n_distinct(TransactionNo)) %>%
arrange(-Average_Order_Value) %>%
head(5)
Top_Average_Order_Value_Fig <- plot_ly(type='pie', labels=Top_Average_Order_Value$Country,
values= Top_Average_Order_Value$Average_Order_Value,
textinfo='label',
insidetextorientation='centre')
Top_Average_Order_Value_Fig
Average Order Value measures the average value from each transaction performed and Netherlands has the highest Average Order Value which may indicate strong purchasing power from each purchase but the consumer base may not be very large.
# Identifying the average order quantity of the top 5 countries
Top_Average_Order_Quantity <- Completed_Transactions %>%
group_by(Country) %>%
summarise(Average_Order_Size = sum(Quantity)/n_distinct(TransactionNo)) %>%
arrange(-Average_Order_Size) %>%
head(5)
Top_Average_Order_Quantity_Fig <- plot_ly(type = "pie",
labels = Top_Average_Order_Quantity$Country,
values = Top_Average_Order_Quantity$Average_Order_Size,
textinfo = 'label',
insidetextorientation='centre')
Top_Average_Order_Quantity_Fig
Average Order Quantity measures the average quantity of products purchased in each transaction. The top performing countries based on average order value is identical to the average order quantity which indicates that the sales volume may be the main driver of the sales revenue in each country. Moreover, these countries may be potential markets that the E-Commerce platform can profit from.
# Identifying the Purchase Frequency value for the top 5 country
Purchase_Frequency_Breakdown <- Completed_Transactions %>%
group_by(Country) %>%
summarise(Purchase_Frequency = n_distinct(TransactionNo)/n_distinct(CustomerNo)) %>%
arrange(-Purchase_Frequency) %>%
count(Purchase_Frequency > 1.00)
# Table showing the Purchase Frequency Breakdown
Purchase_Frequency_Breakdown %>% rename("No of Country" = "n") %>%
kable(caption = "Purchase Frequency Breakdown") %>%
kable_styling(full_width = F)
| Purchase_Frequency > 1 | No of Country |
|---|---|
| FALSE | 7 |
| TRUE | 31 |
# Table showing the Purchase Frequency of the top 5 countries
Purchase_Frequency_kable <- Completed_Transactions %>%
group_by(Country) %>%
summarise(Purchase_Frequency = n_distinct(TransactionNo)/n_distinct(CustomerNo)) %>%
arrange(-Purchase_Frequency) %>%
head(5) %>%
kable(caption = "Top 5 Purchase Frequency Countries") %>%
kable_styling(full_width = F)
Purchase_Frequency_kable
| Country | Purchase_Frequency |
|---|---|
| EIRE | 21.307692 |
| Netherlands | 10.444444 |
| Iceland | 8.000000 |
| Australia | 6.888889 |
| Germany | 4.978022 |
Out of the 38 countries that have access to this e-commerce platform, on average consumers from 31 countries have purchase frequency higher than 1, meaning consumers from majority of the countries found it useful and convenient to shop on the platform. Moreover, its worth pointing out the outstanding purchase frequency from EIRE which may be a potential target market for the e-commerce platform as consumers has high repeating purchase frequency.
# Top 3 Countries based on Sales Volume
Top3_Sales_Country <- Completed_Transactions %>%
group_by(Country) %>%
summarise(Total_Sales = n_distinct(TransactionNo)) %>%
arrange(-Total_Sales) %>%
head(3)
# Total Revenue based on countries
Top5_Revenue_Country <- Completed_Transactions %>%
group_by(Country) %>%
summarise(Total_Revenue_Dollars = sum(Price * Quantity)) %>%
arrange(-Total_Revenue_Dollars) %>%
head(3)
# Visualizing the top 3 sales volume countries
Top3_Sales_Country_Graph <- Top3_Sales_Country %>% ggplot(aes(x= Country,
y = Total_Sales,
fill = Country)) +
geom_bar(stat ="identity") +
ggtitle("Top 3 Sales Volume Country")
# Visualizing the top 3 sales revenue countries
Top3_Revenue_Country_Graph <- Top5_Revenue_Country %>% ggplot(aes(x= Country,
y = Total_Revenue_Dollars,
fill = Country)) +
geom_bar(stat ="identity") +
ggtitle("Top 3 Sales Revenue Country")
Top3_Sales_Country_Graph
Top3_Revenue_Country_Graph
Based on the analysis, UK is the country with the highest sales revenue and volume which is expected as the B2C is based in UK, thus it would be more widely known by people in UK. An interesting finding is that high sales volume is not necessarily correlated with sales revenue. As it can be shown that the Top 2 and 3 countries for sales revenue and volume are different. This finding provides the E-Commerce business insights on that different strategy is needed to raise revenue and volume in different countries.
Based on the analysis, the domestic market (UK) dominates extensively in terms of sales volume and sales revenue while the top 2 and top 3 markets in terms of sales volume and sales revenue are quite far behind. However, based on the chosen KPI’s, entering international markets to take advantage of strong customer base and satisfaction to gain profits may be a beneficial option as shown by the top 5 countries listed under each KPIs. Moreover, the e-commerce platform should prioritize annual events (Black Friday, Cyber Monday and etc) and try to provide attractive offers that are tailored to each target market’s needs and wants to stay competitive in the market competitions. However, having access to more data would facilitate better analysis and thus better basis for strategy to gain more profits.